#!/usr/bin/env python
#coding=utf-8


import datetime
import pandas as pd
from app.bill.models.bill_instance import BillInstance
from app.bill.models.bill_analysis import BillAnalysis
from app.bill.models.bill_event import BillEvent
from app.bill.utils.tag_department_utils import TagDepartmentUtils
from sqlalchemy.orm import Session
from core.response import resp_500
from core.logger import logger


class BillAnalysisHandler(object):
    def __init__(self, ):
        self.tag_department_utils = TagDepartmentUtils()



    def get_analysis_search(self, db: Session, bill_cycle: str, page: int, per_page: int, field: str, value: str):
        """
        账期内, 账单分析表, 根据表字段-模糊查询, 获取对比账单数据
        """
        try:
            result = BillAnalysis.get_analysis_search(db, bill_cycle, page, per_page, field, value)
            if not result:
                logger.warning(f"get analysis search failed, bill_cycle:{bill_cycle} {field}:{value}")
                return False

        except Exception as e:
            message = f"get analysis search error, bill_cycle:{bill_cycle} {field}:{value}"
            logger.exception(f"{message} => {e}")
            return resp_500(message)

        return result



    def get_analysis_match(self, db: Session, bill_cycle: str, page: int, per_page: int, field: str, value: str):
        """
        账期内, 账单分析表, 根据表字段-完全查询, 获取对比账单数据
        """
        try:
            result = BillAnalysis.get_analysis_match(db, bill_cycle, page, per_page, field, value)
            if not result:
                logger.warning(f"get analysis match failed, bill_cycle:{bill_cycle} {field}:{value}")
                return False

        except Exception as e:
            message = f"get analysis match error, bill_cycle:{bill_cycle} {field}:{value}"
            logger.exception(f"{message} => {e}")
            return resp_500(message)

        return result



    def get_analysis_aggregate(self, db: Session, bill_cycle: str, field: str, page: int, per_page: int):
        """
        账期内, 分析账单表, 根据field字段聚合
        """
        try:
            result = BillAnalysis.get_analysis_aggregate(db, bill_cycle, field, page, per_page)
            if not result:
                logger.warning(f"get analysis aggregate failed, bill_cycle:{bill_cycle} field:{field} {page}:{per_page}")
                return False

        except Exception as e:
            message = f"get analysis aggregate error, bill_cycle:{bill_cycle} field:{field} {page}:{per_page}"
            logger.exception(f"{message} => {e}")
            return resp_500(message)

        return result



    def add_to_database(self, db: Session, bill: dict):
        """
        添加到数据库
        """
        # 过滤差额小于10的实例
        # if abs(bill["differ"]) < 10:
        #     return True


        data = {
            "bill_cycle": bill["bill_cycle"],
            "cloud_platform": bill["cloud_platform"],
            "company": bill["company"],
            "bill_class": bill["bill_class"],
            "environment": bill["environment"],
            "maintain_department": bill["maintain_department"],
            "belong_department": bill["belong_department"],
            "tag_key": bill["tag_key"],
            "tag_value": bill["tag_value"],
            "analysis_class": "增加" if bill["analysis_type"] in ["升级", "新增"] else "减少",
            "analysis_type": bill["analysis_type"],
            "bill_item": bill["bill_item"],
            "account_name": bill["account_name"],
            "product_name": bill["product_name"],
            "instance_id": bill["instance_id"],
            "instance_name": bill["instance_name"],
            "pretax_amount": bill["pretax_amount"],
            "differ_amount": bill["differ"],
            "events": bill["events"],
            "comment": bill["comment"]
        }

        result = BillAnalysis.add(db, data)
        if not result:
            logger.error(f"add bill analysis to database failed")
            return False

        return True




    def parsing_event_infos(self, event_infos):
        event_data = [
            {
                "event_id": event_info["uuid"],
                "event_name": event_info["name"],
                "event_type": event_info["event_type"],
                "relation_id": event_info["relation_id"],
                "info_type": event_info["info_type"],
                "comment": event_info["comment"],
            }
            for event_info in event_infos
        ] 
        return event_data





    def bill_connected_with_events(self, db: Session, bill_cycle: str, bill: dict):
        bill["events"] = []
        
        if bill["analysis_type"] == "无变化":
            return True
        
        # 根据实例id来查询事件
        event_infos = BillEvent.get_object_info_by_item(db, bill_cycle, bill["cloud_platform"], "实例id", bill["instance_id"])
        if event_infos:
            bill["events"] = self.parsing_event_infos(event_infos)
            return True
    
        # 根据实例名称来查询事件
        event_infos = BillEvent.get_object_info_by_item(db, bill_cycle, bill["cloud_platform"], "实例名称", bill["instance_name"])
        if event_infos:
            bill["events"] = self.parsing_event_infos(event_infos)
            return True
        
        # 根据标签来查询事件
        event_infos = BillEvent.get_object_info_by_item(db, bill_cycle, bill["cloud_platform"], "appname标签值", bill["tag_value"])
        if event_infos:
            bill["events"] = self.parsing_event_infos(event_infos)
            return True
        
        # 根据产品名称来查询事件
        event_infos = BillEvent.get_object_info_by_item(db, bill_cycle, bill["cloud_platform"], "产品名称", bill["product_name"])
        if event_infos:
            bill["events"] = self.parsing_event_infos(event_infos)
            return True
        
        return True
        

        
    def month_on_month_current(self, db: Session, bill_cycle: str, current_bills: dict, last_bills: dict, next_bills: dict):
        """
        分析当月的数据
        """
        # 开始对比当月账单数据
        for id, bill in current_bills.items():
            if id in last_bills:
                # 已经存在的实例
                bill["differ"] = bill["pretax_amount"] - last_bills[id]["pretax_amount"]
                if bill["differ"] > 0:
                    # 增加费用
                    bill["analysis_type"] = "升级"
                    bill["comment"] = "配置升级"
                elif bill["differ"] < 0:
                    # 本月费用减少，暂时只能感知费用减少，后续有cmdb，则可以查询此实例现在是否存在
                    if next_bills and id not in next_bills or bill["pretax_amount"]< 0:
                        # 释放实例，退款为负数, 本月没有付款，但实例释放，所以有退款，则费用金额为负数
                        bill["analysis_type"] = "释放"
                        bill["comment"] = "本月释放，没有续费，而且有退款"
                    else:
                        # 减少费用
                        bill["analysis_type"] = "降配"
                        bill["comment"] = "配置降配"
                else:
                        # 费用没有变化
                        bill["analysis_type"] = "无变化"
                        bill["comment"] = "费用没变化"
            else:
                # 新增的产品
                bill["differ"] = bill["pretax_amount"]
                bill["analysis_type"] = "新增"
                bill["comment"] = "新增实例"

            # 关联事件
            self.bill_connected_with_events(db, bill_cycle, bill)
            
            # 添加到数据库
            self.add_to_database(db, bill)
            
        return True




    def month_on_month_last(self, db: Session, bill_cycle: str, current_bills: dict, last_bills: dict, next_bills: dict):
        """
        分析上月的数据
        """

        # 因为上个月释放的实例，在本月需要体现费用减少
        for id, bill in last_bills.items():
            if id not in current_bills and bill["pretax_amount"] != 0:
                # 上个月释放实例，本月没有账单数据，则模拟插入一条虚假的费用账单。
                tmp_bill = bill.copy()
                tmp_bill["bill_cycle"] = bill_cycle
                tmp_bill["differ"] = -bill["pretax_amount"]
                tmp_bill["pretax_amount"] = 0

                if tmp_bill["differ"] > 0:
                    # 上个月释放，有退款，但没有续费，所以上个月的费用是负数，到本月体现就是正数（增加费用）
                    tmp_bill["analysis_type"] = "释放"
                    tmp_bill["comment"] = "上个月已释放，上个月没有续费，且有退款。本月体现增加费用"
                else:
                    # 上个月释放，有续费，则一定使用费用，到本月体现就是负数（减少费用）
                    tmp_bill["analysis_type"] = "释放"
                    tmp_bill["comment"] = "上个月已释放，上个月有续费,。本月体现减少费用"
                    
                # 关联事件
                self.bill_connected_with_events(db, bill["bill_cycle"], tmp_bill)
                
                # 添加到数据库
                self.add_to_database(db, tmp_bill)

        return True


    def month_on_month(self, db: Session, bill_cycle: str):
        """
        分析账期内的账单，相对于上个月变化的情况
        :param db:
        :param bill_cycle:
        :return:
        """
        # 当月的账单
        current_month = datetime.datetime.strptime(bill_cycle, "%Y-%m")
        current_bills = BillInstance.get_all_instance_bill(db, current_month.strftime("%Y-%m"))
        current_bills = {
            f"{bill['company']}:{bill['account_name']}:{bill['belong_department']}:{bill['instance_id']}:{bill['bill_item']}": bill
            for bill in current_bills
        }


        #上个月的账单
        last_month = datetime.date(current_month.year, current_month.month, 1) - datetime.timedelta(days = 1)
        last_bills = BillInstance.get_all_instance_bill(db, last_month.strftime("%Y-%m"))
        last_bills = {
            f"{bill['company']}:{bill['account_name']}:{bill['belong_department']}:{bill['instance_id']}:{bill['bill_item']}": bill
            for bill in last_bills
        }

        #下个月的账单
        next_month = datetime.date(current_month.year, current_month.month, 1) + datetime.timedelta(days = 31)
        next_bills = BillInstance.get_all_instance_bill(db, next_month.strftime("%Y-%m"))
        next_bills = {
            f"{bill['company']}:{bill['account_name']}:{bill['belong_department']}:{bill['instance_id']}:{bill['bill_item']}": bill
            for bill in next_bills
        }

        # 先删除数据库中这个月的数据
        BillAnalysis.delete_cycle(db, bill_cycle)
        
        # 分析当月的数据
        self.month_on_month_current(db, bill_cycle, current_bills, last_bills, next_bills)
        
        # 分析上个月的数据
        self.month_on_month_last(db, bill_cycle, current_bills, last_bills, next_bills)

        logger.info(f"month_on_month:{bill_cycle} compare complete！")
        return True




    def get_text_information(self, db: Session, bill_cycle: str):
        """
        账期内, 账单分析表, 费用变更摘要，方便oa提工单
        :param db:
        :param bill_cycle:
        :return:
        """
        result = {
            "departments": [],
            "text": f"{bill_cycle}大概费用变更金额如下:\n"
        }
        company_data = BillAnalysis.get_group_by_company(db, bill_cycle)
        for company in company_data:
            # 暂时只关注卫盈联公司
            if company['company'] != "卫盈联": continue

            # 打印公司总的变更费用
            result["text"] += f"公司主体:{company['company']},变更总费用:{round(company['differ_total'], 2)} \n"

            analysis_class_data = BillAnalysis.get_group_by_analysis_class(db, bill_cycle, company['company'])
            for analysis_class in analysis_class_data:
                # 打印增加/减少的总费用
                result["text"] += f"\n{analysis_class['analysis_class']}总费用:{round(analysis_class['differ_total'], 2)} \n"

                # 按照资产所属部门进行统计
                department_data = BillAnalysis.get_group_by_belong_department(db, bill_cycle, company['company'],
                                                                              analysis_class['analysis_class'])
                for department in department_data:
                    # 打印每个部门的总费用情况
                    result["text"] += f"{department['belong_department']}  {department['differ_total']} = "
                    product_data = BillAnalysis.get_group_by_product_name(db, bill_cycle, company['company'],
                                                                           analysis_class['analysis_class'],
                                                                           department['belong_department'])
                    # 每个部门具体每个产品的变更情况
                    for product in product_data:
                        if not result["text"].endswith("= "): result["text"] += " + "
                        result["text"] += f"{product['product_name'].replace(' ', '')}:{product['differ_total']}"

                    result["text"] += "\n"

                    # 记录所有出现的部门，方便后续数据输出到excel文件
                    if department['belong_department'] not in result["departments"]: result["departments"].append(department['belong_department'])

        logger.info(f"get text information success, bill_cycle:{bill_cycle}")
        return result



    def get_departments_to_excel(self, db: Session, bill_cycle: str, departments: list):
        """
        将指定部门的差异账单导出到excel文件
        """

        excel_writer = pd.ExcelWriter(f"bill_compare_{bill_cycle}.xlsx")
        for department in departments:
            bills = BillAnalysis.get_bills_for_belong_department(db, bill_cycle, department)
            df = pd.DataFrame(bills)
            df.to_excel(excel_writer, sheet_name=department, index=False)
        excel_writer.close()

        logger.info(f"get departments bill to_excel success, cycle:{bill_cycle}")


# 按照资产所管部门进行统计
# department_data = self.get_max_department(db, bill_cycle, company['company'], analysis_class['analysis_class'])

    # def get_max_department(self, db: Session, bill_cycle: str, company: str, analysis_class: str):
    #     """
    #     计算变更费用最大的所管部门， 废弃代码，可以直接：order_by(order).limit(5)完成
    #
    #     例如： cycle=2021-10 company=welab analysis_class=increase
    #          计算2021-10月，welab公司，增长费用最高的5个部门
    #     """
    #     max = 5
    #     departments = BillAnalysis.get_group_by_department(db, bill_cycle, company, analysis_class)
    #     department_data = []
    #     for department in departments:
    #         # 少于max，直接加入队列
    #         if len(department_data) < max:
    #             department_data.append(department)
    #             continue
    #
    #         # 从队列里找到最小的
    #         min = department_data[0]
    #         for department_tmp in department_data:
    #             if abs(department_tmp["differ_total"]) < abs(min["differ_total"]):
    #                 min = department_tmp
    #
    #         # 如果大于最小的，则删除后添加
    #         if abs(department["differ_total"]) > abs(min["differ_total"]):
    #             department_data.remove(min)
    #             department_data.append(department)
    #
    #     return department_data
